package com.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;


import com.entity.Apply;
import com.entity.Deptrole;
import com.entity.User;
import com.service.CalculateSalary;
import com.util.DBUtil;


public class HumanDao {
	public static int count = 0;
	//获取申请列表
	@SuppressWarnings("resource")
	public static ArrayList<Apply> getAllApply(String status,String applytype,int page,int limit){
		ArrayList<Apply> applylist = new ArrayList<Apply>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		Apply apply = null;
		String sql = null;
		//获取数据
		System.out.println("applytype:"+applytype);
		String iscondition = "yes";
		if(status == null || status.length() == 0) {
			iscondition = "no";
		}
		System.out.println("iscondition:"+iscondition);
		try {
			switch(iscondition) {
			case "yes":
				sql = "select * from apply where apply.status=? and applytype=? limit ?,?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, status);
				pst.setString(2, applytype);
				pst.setInt(3, page);
				pst.setInt(4, limit);
				break;
			default :
				sql = "select * from apply where applytype=? limit ?,?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, applytype);
				pst.setInt(2, page);
				pst.setInt(3, limit);
				break;
			}
			rs = pst.executeQuery();
			System.out.println("Human pst="+pst);
			while(rs.next()) {
				apply = new Apply();
				apply.setApplyid(rs.getInt("Applyid"));
				apply.setApplicant(rs.getString("Applicant"));
				apply.setApplytime(rs.getString("Applytime"));
				apply.setStatus(rs.getString("Apply.Status"));
				apply.setPower(rs.getString("Power"));
				apply.setOperater(rs.getString("Operater"));
				apply.setHandle(rs.getString("Handle"));
				apply.setHandletime(rs.getString("Handletime"));
				apply.setOvertime(rs.getInt("Apply.Overtime"));
				apply.setWeekovertime(rs.getInt("Apply.Weekovertime"));
				apply.setFestovertime(rs.getInt("Apply.Festovertime"));
				apply.setLeavecount(rs.getInt("Apply.Leavecount"));
				apply.setAbsentcount(rs.getInt("Apply.Absentcount"));
				apply.setFestivalbonus(rs.getFloat("Apply.Festivalbonus"));
				apply.setYearbonus(rs.getFloat("Apply.Yearbonus"));
				apply.setCommission(rs.getFloat("Apply.Commission"));
				apply.setApplydescribe(rs.getString("Apply.Applydescribe"));
				apply.setApplytype(rs.getString("Apply.Applytype"));
				applylist.add(apply);
			}
//			System.out.println(applylist.get(0).getApplyid() + "***" + applylist.get(0).getStatus());
		}catch(SQLException e) {
			e.printStackTrace();
		}
		//获取数据数
		try {
			switch(iscondition) {
			case "yes":
				sql = "select * from apply where apply.status=? and applytype=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, status);
				pst.setString(2, applytype);
				break;
			default :
				sql = "select * from apply where applytype=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, applytype);
				break;
			}
			rs = pst.executeQuery();
			System.out.println("Human pst="+pst);
			rs.last();
			count = rs.getRow();
			System.out.println("count:"+count);
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return applylist;
	}
	//人力部门处理申请
	@SuppressWarnings("resource")
	public static boolean doApply(String status,String handle,String operater,int applyid,String applytype,int overtime,int weekovertime,int festovertime,int leavecount,int absentcount,int commission,int festivalbonus,int yearbonus ) {
		boolean fapply = false;
		boolean fuser = false;
		PreparedStatement pst = null;
		String sql = null;
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
		String nowtime = df.format(new Date());// new Date()为获取当前系统时间
		System.out.println("DAO参数："+status+"  "+applyid+"  "+operater+"  "+handle+"  "+applytype);
		try {
			//修改申请表
			sql = "UPDATE apply set status=?,handle=?,handletime=? where applyid=?";
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setString(1, status);
			pst.setString(2, handle);
			pst.setString(3, nowtime);
			pst.setInt(4, applyid);
			System.out.println("Human 修改审核状态："+pst);
			fapply = (pst.executeUpdate()!=0);
			//当同意申请时，即status=1否则=2，修改user表，申请数据实际生效
			if("1".equals(status)) {
				if("inputOvertime".equals(applytype)) {
					sql = "UPDATE user set overtime=overtime+?,weekovertime=weekovertime+?,festovertime=festovertime+? where userid=?";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setInt(1, overtime);
					pst.setInt(2, weekovertime);
					pst.setInt(3, festovertime);
					pst.setString(4, operater);
					System.out.println("Human 加班申请 写进用户表里："+pst);
					fuser = (pst.executeUpdate()!=0);
				}else if("inputPunishing".equals(applytype)) {
					sql = "UPDATE user set leavecount=leavecount+?,absentcount=absentcount+? where userid=?";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setInt(1, leavecount);
					pst.setInt(2, absentcount);
					pst.setString(3, operater);
					System.out.println("Human 请假旷工申请 写进用户表里："+pst);
					fuser = (pst.executeUpdate()!=0);
				}else if("inputCommission".equals(applytype)) {
					sql = "UPDATE user set commission=? where userid=?";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setInt(1, commission);
					pst.setString(2, operater);
					System.out.println("Human 提成申请 写进用户表里："+pst);
					fuser = (pst.executeUpdate()!=0);
				}else if("inputBonus".equals(applytype)) {
					sql = "UPDATE user set festivalwages=festivalwages+?,yearendwages=yearendwages+? where userid=?";
					pst = DBUtil.getConnection().prepareStatement(sql);
					pst.setInt(1, festivalbonus);
					pst.setInt(2, yearbonus);
					pst.setString(3, operater);
					System.out.println("Human 奖金申请 写进用户表里："+pst);
					fuser = (pst.executeUpdate()!=0);
				}
			}else {
				fuser = true;
			}
		} catch(SQLException e) {
			e.printStackTrace();
			System.out.println("处理申请出现两个或一个失败");
		}
		if(fapply&&fuser) {
			return true;
		}else {
			return false;
		}
	}
	//返回全员User信息，支持条件查询和分页
	@SuppressWarnings("resource")
	public static ArrayList<User> getUserInfo(String condition,String conditionValue,int page,int limit) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		ArrayList<User> userlist = new ArrayList<User>();
		User user = null;
		String sql = null;
		if(condition == null) {
			condition = "";
		}
		//获取数据
		try {
			switch(condition) {
			case "deptname":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and deptname like ? limit ?,?;";
				
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, '%'+conditionValue+'%');
				pst.setInt(2, page);
				pst.setInt(3, limit);
				break;
			case "rolename":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and rolename like ? limit ?,?;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, '%'+conditionValue+'%');
				pst.setInt(2, page);
				pst.setInt(3, limit);
				break;
			case "userid":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and userid like ? limit ?,?;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, '%'+conditionValue+'%');
				pst.setInt(2, page);
				pst.setInt(3, limit);
				break;
			default :
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid limit ?,?;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setInt(1, page);
				pst.setInt(2, limit);
				break;
			}
			System.out.println("sql="+pst);
			rs = pst.executeQuery();
			while(rs.next()) {
				user = new User();
				user.setUserid(rs.getString("Userid"));
				user.setDeptid(rs.getString("Deptid"));
				user.setDeptname(rs.getString("Deptname"));
				user.setRoleid(rs.getString("Roleid"));
				user.setPwd(rs.getString("Pwd"));
				user.setRolename(rs.getString("Rolename"));
				user.setStatus(rs.getString("Status"));
				user.setName(rs.getString("Name"));
				user.setSex(rs.getString("Sex"));
				user.setAge(rs.getInt("Age"));
				user.setPhone(rs.getString("Phone"));
				user.setCreatetime(rs.getString("Create_time"));
				user.setBasewages(rs.getFloat("Basewages"));
				user.setMeritwages(rs.getFloat("Meritwages"));
				user.setPunishingwages(rs.getFloat("Punishingwages"));
				user.setTotalwages(rs.getFloat("Totalwages"));
				user.setOvertime(rs.getInt("Overtime"));
				user.setWeekovertime(rs.getInt("Weekovertime"));
				user.setFestovertime(rs.getInt("Festovertime"));
				user.setOvertimestandard(rs.getFloat("Overtimestandard"));
				user.setCommission(rs.getFloat("Commission"));
				user.setFestivalwages(rs.getFloat("Festivalwages"));
				user.setYearendwages(rs.getFloat("Yearendwages"));
				user.setLeavecount(rs.getInt("Leavecount"));
				user.setAbsentcount(rs.getInt("Absentcount"));
				user.setLeavestandard(rs.getFloat("Leavestandard"));
				user.setAbsentstandard(rs.getFloat("Absentstandard"));
				user.setIspay(rs.getString("Ispay"));
				user = CalculateSalary.calculate(user);
				userlist.add(user);
			//	剩余权限数据...
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
		//获取数据
		try {
			switch(condition) {
			case "deptname":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and deptname like ?;";
				
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, '%'+conditionValue+'%');
				break;
			case "rolename":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and rolename like ?;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, '%'+conditionValue+'%');
				break;
			case "userid":
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid and userid like ?;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, '%'+conditionValue+'%');
				break;
			default :
				sql = "select * from user,deptrole where user.deptid=deptrole.deptid and user.roleid=deptrole.roleid;";
				pst = DBUtil.getConnection().prepareStatement(sql);
				break;
			}
			rs = pst.executeQuery();
			System.out.println("Human pst="+pst);
			rs.last();
			count = rs.getRow();
			System.out.println("count:"+count);
		} catch(SQLException e) {
			e.printStackTrace();
		}
		return userlist;
	}
	//为了动态下拉列表
	public static ArrayList<Map<String,String>> getDeptRole(String getType) {
		PreparedStatement pst = null;
		ResultSet rs = null;
		ArrayList<Map<String,String>> deptrolelist = new ArrayList<Map<String,String>>();
		String sql = "";
		//查部门
		if("dept".equals(getType)) {
			sql = "SELECT distinct deptid,deptname FROM `deptrole` ";
			try {
				pst = DBUtil.getConnection().prepareStatement(sql);
				rs = pst.executeQuery();
				while(rs.next()) {
					Map<String,String> map = new HashMap<>();
					map.put("itemId", rs.getString("deptid"));
					map.put("itemName", rs.getString("deptname"));
					deptrolelist.add(map);
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
		//查角色
		} else if("role".equals(getType)) {
			sql = "SELECT distinct roleid,rolename FROM `deptrole` ";
			try {
				pst = DBUtil.getConnection().prepareStatement(sql);
				rs = pst.executeQuery();
				while(rs.next()) {
					Map<String,String> map = new HashMap<>();
					map.put("itemId", rs.getString("roleid"));
					map.put("itemName", rs.getString("rolename"));
					deptrolelist.add(map);
				}
			} catch(SQLException e) {
				e.printStackTrace();
			}
		}
		return deptrolelist;
	}
	//由部门id与职位id 得各项薪资标准
	public static Deptrole getStandard(int deptid,int roleid) {
		Deptrole deptrole = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = null;
		try {
			sql = "SELECT * FROM deptrole where deptid=? and roleid=?";
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setInt(1, deptid);
			pst.setInt(2, roleid);
			rs = pst.executeQuery();
			while(rs.next()) {
				deptrole = new Deptrole();
				deptrole.setBasewages(rs.getFloat("Basewages"));
				deptrole.setOvertimestandard(rs.getFloat("Overtimestandard"));
				deptrole.setLeavestandard(rs.getFloat("Leavestandard"));
				deptrole.setAbsentstandard(rs.getFloat("Absentstandard"));
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		return deptrole;
	}
	//添加新用户
	public static boolean register(User user) {
		boolean action=false;
		PreparedStatement pst = null;
		String sql = "insert into User"
				+ "(Userid,Deptid,Roleid,Pwd,Status,Name,Sex,Age,Phone,Basewages,"
				+ "Overtimestandard,Leavestandard,Absentstandard,Ispay)"
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,'0')";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setString(1, user.getUserid());
			pst.setString(2, user.getDeptid());
			pst.setString(3, user.getRoleid());
			pst.setString(4, user.getPwd());
			pst.setString(5, user.getStatus());
			pst.setString(6, user.getName());
			pst.setString(7, user.getSex());
			pst.setInt(8, user.getAge());
			pst.setString(9, user.getPhone());
			pst.setFloat(10, user.getBasewages());
			pst.setFloat(11, user.getOvertimestandard());
			pst.setFloat(12, user.getLeavestandard());
			pst.setFloat(13, user.getAbsentstandard());
			action = (pst.executeUpdate()!=0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return action;
	}	
	//删除用户
	public static boolean deleteUser(String operater) {
		boolean action = false;
		PreparedStatement pst = null;
		String sql = "delete from user where userid=?";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setString(1, operater);
			action = (pst.executeUpdate()!=0);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return action;
	}
	//财务部发放薪资
	public static boolean grantSalary(String operater) {
		boolean action = false;
		PreparedStatement pst = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM apply where operater=? and status=0";
		try {
			pst = DBUtil.getConnection().prepareStatement(sql);
			pst.setString(1, operater);
			rs = pst.executeQuery();
			rs.last();
			if(rs.getRow() == 0) {
				sql = "update user set ispay=1 where userid=?";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, operater);
				action = (pst.executeUpdate()!=0);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return action;
	}
	//为发放薪资图标 获取数据
	public static Map<String,Object> getStatistic() {
		Map<String,Object> map = new HashMap<String,Object>();
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			//获取x轴
			ArrayList<String> deptlist = new ArrayList<String>();
			String sql = "SELECT distinct deptname FROM `deptrole`";
			pst = DBUtil.getConnection().prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()) {
				deptlist.add(rs.getString("deptname"));
			}
			map.put("deptlist", deptlist);
			//获取总薪资
			ArrayList<String> totallist = new ArrayList<String>();
			ArrayList<String> meritlist = new ArrayList<String>();
			ArrayList<String> punishlist = new ArrayList<String>();
			for(int i=0;i<deptlist.size();i++) {
				sql = "SELECT SUM(Totalwages) TotalwagesSum,SUM(Meritwages) MeritwagesSum,SUM(Punishingwages) PunishingwagesSum FROM user,deptrole where ispay=1 and deptname=? and user.Deptid=deptrole.deptid";
				pst = DBUtil.getConnection().prepareStatement(sql);
				pst.setString(1, deptlist.get(i));
				rs = pst.executeQuery();
				while(rs.next()) {
					totallist.add(rs.getString("TotalwagesSum"));
					meritlist.add(rs.getString("MeritwagesSum"));
					punishlist.add(rs.getString("PunishingwagesSum"));
				}
			}
			map.put("totallist", totallist);
			map.put("meritlist", meritlist);
			map.put("punishlist", punishlist);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return map;
	}

}
